﻿/**************************************************************************************************************************/
/* Copyright (c) 2011 Krzysztof Kozielczyk, under this MIT-style open source license                                      */
/*                                                                                                                        */
/* Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated           */
/* documentation files (the "Software"), to deal in the Software without restriction, including without limitation the    */
/* rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit */
/* persons to whom the Software is furnished to do so, subject to the following conditions:                               */
/*                                                                                                                        */
/* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the   */
/* Software.                                                                                                              */
/*                                                                                                                        */
/* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE   */
/* WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR  */
/* COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR       */
/* OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.       */
/**************************************************************************************************************************/

using System;
using System.Data.SqlClient;
using System.Text;


public partial class ExecuteSQL : System.Web.UI.Page
{
    // We will build connection strings form this template, using string.Format() method and passing Server-Name and Database-Name as the first and second argument 
    private const string ConnectionStringTemplate = @"Server={0};Database={1}";

    // Our Server value will default to the Automatic Instance of LocalDB
    private const string DefaultServer = @"(localdb)\v11.0";

    private void LoadDatabases()
    {
        // Preserving the database selection, so we can reset it afterwards
        string selectedDatabase = lstDatabases.SelectedValue;

        lstDatabases.Items.Clear();

        // Connect to master database on the target server and get the list of databases
        // Re-populate lstDatabases drop-down list with the list of databases
        using (var sqlConnection = new SqlConnection(string.Format(ConnectionStringTemplate, DefaultServer, "master")))
        {
            sqlConnection.Open();

            using (var sqlExecuteCommand = new SqlCommand("select name from sys.databases", sqlConnection))
            {
                var sqlReader = sqlExecuteCommand.ExecuteReader();

                while (sqlReader.Read())
                {
                    lstDatabases.Items.Add(sqlReader.GetString(0));
                }
            }

            sqlConnection.Close();
        }

        // Re-selecting the database, provided it still exists
        if(lstDatabases.Items.FindByValue(selectedDatabase) != null)
            lstDatabases.SelectedValue = selectedDatabase;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            LoadDatabases();
        }
        catch (Exception error)
        {
            // Minimum viable error handling :-)
            txtSqlResult.Text = error.Message;
        }
    }

    protected void ExecuteSqlButton_Click(object sender, EventArgs e)
    {
        try
        {
            // Let's spare the garbage collector some work and use StringBuilders instead of string concatenation :-)
            var resultString = new StringBuilder();
            var infoMessagesString = new StringBuilder();

            using (var sqlConnection = new SqlConnection(string.Format(ConnectionStringTemplate, DefaultServer, lstDatabases.SelectedValue)))
            {
                // Hooking up warning handling and redirecting warning messages to the results field
                sqlConnection.InfoMessage += delegate(object infoMessageSender, SqlInfoMessageEventArgs infoMessageArgs)
                {
                    infoMessagesString.AppendLine(infoMessageArgs.Message);
                };

                sqlConnection.Open();

                // Execute the script that was typed in into txtSqlScript field
                using (var sqlExecuteCommand = new SqlCommand(txtSqlScript.Text, sqlConnection))
                {
                    var sqlReader = sqlExecuteCommand.ExecuteReader();

                    // Probably could use printing column headers, but will not bother for now
                    var sqlResults = new object[sqlReader.FieldCount];
                    while (sqlReader.Read())
                    {
                        // Printing space-separated list of values, one line per row
                        sqlReader.GetSqlValues(sqlResults);

                        foreach (var resultValue in sqlResults)
                        {
                            resultString.Append(resultValue);
                            resultString.Append(' ');
                        }

                        resultString.AppendLine();
                    }
                }

                sqlConnection.Close();
            }

            infoMessagesString.Append(resultString);

            // Some commands don't return anything
            // Appending some indication that the command completed successfully
            infoMessagesString.AppendLine();
            infoMessagesString.AppendLine("Status: Operation completed successfuly!");

            // Funneling all the results into txtSqlResult field
            txtSqlResult.Text = infoMessagesString.ToString();
        }
        catch (Exception error)
        {
            // Elaborate error handling and reporting code :-)
            txtSqlResult.Text = error.Message;
        }
    }
}